-- Copyright (c) Microsoft Corporation. All rights reserved.

/*******************************************************************************************/
/* prc_iiCreateWssListItem */
/* */
/* Stores the relavent data about the WSS list item in a SQL table.  This table reflects the  */
/* current state of the WSS server.  This table can then be compared against a master version */
/* of the data to identify the operations that need to occur  */
/*******************************************************************************************/

CREATE PROCEDURE [dbo].[prc_iiCreateWssListItem]
	@SessionID [nvarchar](max),
	@FileID [int],
	@LatestVersion int,
	@FilePath [nvarchar](260),
	@FileName [nvarchar](128),
	@ParentPath [nvarchar](260),
	@FSObjectType int,
	@Modified [datetime],
	@Editor [nvarchar](max)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

INSERT 
INTO [WSSCurrent]
	(
		SessionId,
		FileId,
		LatestVersion,
		FilePath,
		[FileName],
		ParentPath,
		FSObjectType,
		Modified,
		Editor
	)
     VALUES
	(	 
	 @SessionId,
     @FileId,
     @LatestVersion,
	 @FilePath,
	 @FileName,
	 @ParentPath,
	 @FSObjectType,
	 @Modified,
	 @Editor
	)

SELECT @@IDENTITY

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'INSERT', N'WSSCurrent')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiCreateWssListItem TO UPSDEXEC
GO

/*******************************************************************************************/
/* prc_iiUpdateMasterSessionRow */
/* */
/* Update the master table with relavent data about the WSS list item in a SQL table.      */
/*******************************************************************************************/

CREATE PROCEDURE [dbo].prc_iiUpdateMasterSessionRow
	@SessionID [nvarchar](max),
	@FileID [int],
	@LatestVersion int,
	@FilePath [nvarchar](260),
	@FileName [nvarchar](128),
	@ParentPath [nvarchar](260),
	@FSObjectType int,
	@Modified [datetime],
	@Editor [nvarchar](max)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

Delete 
FROM [WSSMaster]
where FileID = @FileID

INSERT 
INTO [WSSMaster]
	(
		SessionId,
		FileId,
		LatestVersion,
		FilePath,
		[FileName],
		ParentPath,
		FSObjectType,
		Modified,
		Editor
	)
     VALUES
	(	 
	 @SessionId,
     @FileId,
     @LatestVersion,
	 @FilePath,
	 @FileName,
	 @ParentPath,
	 @FSObjectType,
	 @Modified,
	 @Editor
	)

SELECT @@IDENTITY

SET @errorstatus = @@ERROR
IF (@errorstatus <> 0)
BEGIN
    RAISERROR (500004, 16, -1, @procedureName, @errorstatus, N'INSERT', N'WSSMaster')
    RETURN 500004
END
GO

GRANT EXECUTE ON prc_iiUpdateMasterSessionRow TO UPSDEXEC
GO

/*******************************************************************************************/
/* prc_FindWssChanges */
/* */
/* Compares the current state data against the master data and produces */
/*******************************************************************************************/
CREATE PROCEDURE [dbo].[prc_FindWssChanges]
	@SessionID [nvarchar](max)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

-- added WSS items

SELECT c.*, 0 AS PreviousVersion FROM WSSCurrent c
WHERE c.SessionId=@SessionId
AND NOT EXISTS 
(SELECT FileID FROM WSSMaster m
WHERE m.FileID=c.FileID AND m.SessionId=@SessionId)

-- edited items

SELECT c.*, m.LatestVersion AS PreviousVersion FROM WSSCurrent c
JOIN WSSMaster m ON c.FileID=m.FileID AND c.SessionId=m.SessionId
WHERE 
c.SessionId=@SessionId  -- filter to this session
AND c.FilePath=m.FilePath  -- ignore renames, even with edits they are in another group
AND c.LatestVersion > m.LatestVersion  -- if it goes backwards we probably had a restore.  assert this?

-- deleted WSS items

SELECT m.*, 0 AS PreviousVersion FROM WSSMaster m
WHERE m.SessionId=@SessionId
AND NOT EXISTS 
(SELECT FileID FROM WSSCurrent c
WHERE m.FileID=c.FileID  AND c.SessionId=@SessionId)

-- renamed WSS items (old and new names included)

SELECT	0,		-- this is the ID column.  It is unused but needed for ordinal offsets
		c.SessionId,
		c.FileID,
		c.LatestVersion,
		c.FilePath,
		c.[FileName],
		c.ParentPath,
		c.FSObjectType,
		c.Modified,
		c.Editor,
		m.LatestVersion AS PreviousVersion,
		m.FilePath AS OldFilePath,
		m.[FileName] AS OldFileName,
		m.ParentPath AS OldParentPath
FROM WSSCurrent c
JOIN WSSMaster m ON c.FileID=m.FileID AND c.SessionId=m.SessionId
WHERE c.FilePath<>m.FilePath
AND c.SessionId=@SessionId
GO

GRANT EXECUTE ON prc_FindWssChanges TO UPSDEXEC
GO

/*****************************************************************************************/

CREATE PROCEDURE [dbo].[prc_iiPromoteCurrentToMaster]
	@SessionID [nvarchar](max)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

DELETE FROM WSSMaster
WHERE SessionID=@SessionId

INSERT INTO WSSMaster (SessionID, FileID, LatestVersion, FilePath, [FileName], ParentPath, FSObjectType, Modified, Editor)
SELECT SessionID, FileID, LatestVersion, FilePath, [FileName], ParentPath, FSObjectType, Modified, Editor
FROM WSSCurrent
WHERE SessionID=@SessionId

DELETE FROM WSSCurrent
WHERE SessionID=@SessionId
GO

GRANT EXECUTE ON prc_iiPromoteCurrentToMaster TO UPSDEXEC
GO

/*****************************************************************************************/

CREATE PROCEDURE [dbo].[prc_iiClearWSSCurrentTable]
	@SessionID [nvarchar](max)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

DELETE FROM WSSCurrent
WHERE SessionID=@SessionId

GRANT EXECUTE ON prc_iiClearWSSCurrentTable TO UPSDEXEC
GO


/*****************************************************************************************/
/* Update the WSSMaster table with rename actions*/
/*****************************************************************************************/
CREATE PROCEDURE [dbo].[prc_iiUpdateWSSMasterTableWithRename]
	@SessionID [nvarchar](max),
	@OldFilePath [nvarchar](260),
	@NewFilePath [nvarchar](260),
	@FileName [nvarchar](128)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

UPDATE WssMaster 
SET FilePath=@NewFilePath, FileName=@FileName  
WHERE FilePath=@OldFilePath AND SessionId=@SessionID

GRANT EXECUTE ON prc_iiUpdateWSSMasterTableWithRename TO UPSDEXEC
GO

/*****************************************************************************************/
/* Update the WSSMaster table with delete actions*/
/*****************************************************************************************/
CREATE PROCEDURE [dbo].[prc_iiUpdateWSSMasterTableWithDelete]
	@SessionID [nvarchar](max),
	@FilePath [nvarchar](260)
AS
DECLARE @errorstatus         INT

-- Initialize the ProcedureName for error messages.
DECLARE @procedureName SYSNAME
SELECT  @procedureName = @@SERVERNAME + '.' + db_name() + '..' + object_name(@@PROCID)

IF (@@TRANCOUNT = 0)
BEGIN
    RAISERROR (500002, 16, -1, @procedureName)
    RETURN 500002
END

DELETE FROM WssMaster 
WHERE FilePath=@FilePath AND SessionId=@SessionID

GRANT EXECUTE ON prc_iiUpdateWSSMasterTableWithDelete TO UPSDEXEC
GO

